package com.weng.druid.parse;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLInListExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.weng.coder.common.utils.Constants;
import com.weng.coder.common.utils.DbUtils;
import com.weng.coder.common.utils.NameConvertUtils;
import com.weng.coder.entity.CoderCondition;
import com.weng.coder.entity.Field;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author wengzhonghui
 * @date 2021/1/13 16:57
 */
@Slf4j
public class ParseSelectSqlTest4 {



		@Test
		public void testParse(){
				 // 可以是ORACLE、POSTGRESQL、SQLSERVER、ODPS等
//				String sql = "CREATE TABLE `platform_team` (   `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',   `team_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '团队名称',   `owner_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '-1' COMMENT '创建用户Id',   `owner_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建用户名称',   `is_del` tinyint(1) DEFAULT '0' COMMENT '删除状态1册除0未删除',   `create_time` datetime DEFAULT NULL COMMENT '创建时间',   `update_time` datetime DEFAULT NULL COMMENT '修改时间',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队信息';";
//				String sql1 = "CREATE TABLE `platform_team1` (   `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',   `team_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '团队名称',   `owner_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '-1' COMMENT '创建用户Id',   `owner_name` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建用户名称',   `is_del` tinyint(1) DEFAULT '0' COMMENT '删除状态1册除0未删除',   `create_time` datetime DEFAULT NULL COMMENT '创建时间',   `update_time` datetime DEFAULT NULL COMMENT '修改时间',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队信息';";
				String sql3 = "SELECT c.*, `ID`,`project_id` projectId,`NAME`,`CODE`,`PARENT_ID`,`POSITION`,d.`author_username`  FROM  `api_catalog` c INNER JOIN `api_doc` d ON c.`ID`= d.`id`;";
				String sql4 = "CREATE TABLE test_metadata  AS  SELECT a,b,c FROM `md_collect_log` l INNER JOIN `md_collect_task` t ON l.id=t.id WHERE l.id='22'";
				String sql5 = "create table base_datameta1 as SELECT cata.NAME catalogName, p.progress, c.name FROM (\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_stu_rel stuRel ON c.id = stuRel.course_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.stu_id= '1'\n" +
								"            UNION\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_class_rel classRel ON classRel.course_id = c.id\n" +
								"            INNER JOIN edu_student_class_rel stuRel ON stuRel.class_id = classRel.class_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.user_id = '1'\n" +
								"        ) c\n" +
								"        LEFT JOIN edu_catalog cata ON c.catalog_id=cata.ID\n" +
								"        LEFT JOIN edu_course_study_progress p ON c.id = p.course_id AND p.stu_id = '1'\n" +
								"        WHERE 1=1 AND p.course_id = '11'";
				String sql6 = "INSERT INTO base_datameta1 (catalogName,progress,name)  SELECT cata.NAME catalogName, p.progress, c.name FROM (\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_stu_rel stuRel ON c.id = stuRel.course_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.stu_id= '1'\n" +
								"            UNION\n" +
								"            SELECT c.* FROM edu_course c\n" +
								"            INNER JOIN edu_course_class_rel classRel ON classRel.course_id = c.id\n" +
								"            INNER JOIN edu_student_class_rel stuRel ON stuRel.class_id = classRel.class_id\n" +
								"            WHERE c.is_del=0 AND c.is_publish='1' AND stuRel.user_id = '1'\n" +
								"        ) c\n" +
								"        LEFT JOIN edu_catalog cata ON c.catalog_id=cata.ID\n" +
								"        LEFT JOIN edu_course_study_progress p ON c.id = p.course_id AND p.stu_id = '1'\n" +
								"        WHERE 1=1 AND p.course_id = '11'";
				String sql7 = "INSERT INTO sebsites(name, country)  SELECT name, country FROM apps";
				String sql8 = "insert into table_test_2 (farendma, hesuandm, hesuanmc, weihguiy, weihjigo, weihriqi, shijchuo) values\n" +
								"('99996','HS205301','代码1','S####','101001','20140101',1414673101376), \n" +
								"('99996','HS205401','代码2','S####','101001','20140101',1414673101376);";

				String sql9 ="create table qualitis_logic_p004_1612404361618_59200 as select * from warehouse.logic_p004 where null";
				String select10 = "SELECT s.`name` datasourceName,t.*,t.`data_source_id`,t.`is_collected` FROM `collect_table` t LEFT JOIN `collect_data_source` s ON t.`data_source_id` = s.`id`  WHERE t.`data_source_id` = '1379749996597170178' AND t.`is_collected` =:isCollectedTest  AND t.`is_increment`=? AND t.`collect_task_status` = 10 AND t.`is_schedule_config` = 0  AND t.`is_del` = ?";

//			MySqlStatementParser parser = new MySqlStatementParser(sql9);
//			System.out.println(parser);
				getTablesBySQLOfSelect(select10);
//				getTablesBySQL(sql4);
//				getTablesBySQL(sql5);
//				getTablesBySQL(sql6);
//				getTablesBySQL(sql7);
//				getTablesBySQL(sql8);

		}



		/*
		 * mysql的解析不支持SQLUnionQueryTableSource，所以使用hive
		 * @param [sql]
		 * @return
		 */
		public void getTablesBySQLOfSelect(String sql){
				MySqlStatementParser parser = new MySqlStatementParser(sql);
				SQLStatement sqlStatement = parser.parseStatement();
				MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor ();
				sqlStatement.accept(visitor);
				Map<TableStat.Name, TableStat> tableStatMap = visitor.getTables();
				for(Map.Entry<TableStat.Name, TableStat> tableStatEntry: tableStatMap.entrySet()){
					System.out.println("表名：" + tableStatEntry.getKey().getName());
					System.out.println("操作名：" + tableStatEntry.getValue());
				}

				if(sqlStatement instanceof SQLSelectStatement){
					SQLSelectStatement sqlSelectStatement = (SQLSelectStatement)sqlStatement;

					// 获取select中的属性
					List<Field> fields = getFieldListBySelect(sqlSelectStatement);
					// 获取查询条件中的查询条件
					List<CoderCondition> coderConditions = getWhereCondition(sqlSelectStatement);

				}

		}

		/**
		 * 获取查询条件中，需要查询的属性(也就是查询条件中带?号的，例如where aa=?中的aa)
		 * @param sqlSelectStatement
		 * @return
		 */
		private List<CoderCondition> getWhereCondition(SQLSelectStatement sqlSelectStatement){
			List<CoderCondition> coderConditions = new ArrayList<>();
			SQLBinaryOpExpr whereExpr = (SQLBinaryOpExpr)sqlSelectStatement.getSelect().getQueryBlock().getWhere();
			getWhereExpr(whereExpr, coderConditions);
			return coderConditions;
		}

		/**
		 *  获取查询语句中的属性列表
		 * @param sqlSelectStatement
		 * @return
		 */
		private List<Field> getFieldListBySelect(SQLSelectStatement sqlSelectStatement){
				List<Field> fields = new ArrayList<>();
				List<SQLSelectItem> list = (List<SQLSelectItem>)sqlSelectStatement.getSelect().getQueryBlock().getSelectList();
				for(SQLSelectItem sqlSelectItem : list){
					String column = "";
					if(sqlSelectItem.getAlias()!=null){
						column = sqlSelectItem.getAlias();
					}else{
						column = sqlSelectItem.getExpr().toString();
					}
					if(column.indexOf(".*")>=0){
						continue;
					}
					// 处理d.author_username这种场景，只返回author_username
					if(column.indexOf(".")>=0){
						column = column.substring(column.indexOf(".") + 1, column.length());
					}
					fields.add(getDefaultField(column));
				}
				// 处理select * 的特殊场景,没有字段，则加一个默认的字段
				if(fields.size() == 0){
					fields.add(getDefaultField("no_column"));
				}
				return fields;
			}

		/*
		 * 通过列名，获取Field  对象
		 * @param [columnName]
		 * @return
		 */
		private Field getDefaultField(String columnName){
			Field field = new Field();
			columnName = DbUtils.clearSpecialCharacters(columnName);
			field.setName(NameConvertUtils.underlineToCamel(columnName));
			field.setColumn(columnName);
			field.setJdbcType(Constants.getDefaultJdbcType());
			field.setIsPrimary(Constants.FLAG_FALSE);
			field.setJavaType(Constants.getDefaultJavaType());
			return field;
		}

		/**
		 * 获取带条件中带?号的属性
		 * @param whereExpr, whereColums
		 * @return
		 */
		public void getWhereExpr(SQLBinaryOpExpr whereExpr,List<CoderCondition> whereColums){
			if(whereExpr == null){
				return;
			}
			if(whereExpr.getRight() !=null && whereExpr.getRight() instanceof SQLBinaryOpExpr){
				getWhereExpr((SQLBinaryOpExpr)whereExpr.getRight(), whereColums);
			}
			if(whereExpr.getLeft() !=null && whereExpr.getLeft() instanceof SQLBinaryOpExpr){
				getWhereExpr((SQLBinaryOpExpr)whereExpr.getLeft(), whereColums);
			}
			if(whereExpr.getRight() !=null && whereExpr.getRight() instanceof SQLVariantRefExpr){
				SQLVariantRefExpr sqlVariantRefExpr = (SQLVariantRefExpr)whereExpr.getRight();
				// 属性值为?号，或者以:号开头，则是查询条件
				String proName = sqlVariantRefExpr.getName();
				if( proName!= null
								&& ("?".equalsIgnoreCase(proName) || proName.indexOf(":")==0 )
					&& whereExpr.getLeft() instanceof SQLPropertyExpr){
					SQLPropertyExpr leftPro = (SQLPropertyExpr)whereExpr.getLeft();
					CoderCondition coderCondition = new CoderCondition();
					String jdbcName = leftPro.getName();
					jdbcName = DbUtils.clearSpecialCharacters(jdbcName);
					String javaName = NameConvertUtils.underlineToCamel(jdbcName);
					if(proName.indexOf(":")==0){
						javaName = proName.substring(proName.indexOf(":")+1);
					}
					coderCondition.setJdbcName(jdbcName);
					coderCondition.setJavaName(javaName);
					coderCondition.setJdbcType(Constants.getDefaultJdbcType());
					coderCondition.setJavaType(Constants.getDefaultJavaType());
					whereColums.add(coderCondition);
				}
			}
		}

		public void getTablesBySQLOfMysql(String sql){
				MySqlStatementParser  parser = new MySqlStatementParser(sql);
				SQLStatement sqlStatement = parser.parseStatement();
				MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor ();
				sqlStatement.accept(visitor);
				Map<TableStat.Name, TableStat> tableStatMap = visitor.getTables();
				for(Map.Entry<TableStat.Name, TableStat> tableStatEntry: tableStatMap.entrySet()){
						System.out.println("表名：" + tableStatEntry.getKey().getName());
						System.out.println("操作名：" + tableStatEntry.getValue());
				}
		}


		@Test
		public void testAlter() {
				String sql = "alter table test_check1 ADD index ind1(farendma), add co2 VARCHAR(20), drop INDEX id3, drop column co1;";
				MySqlStatementParser parser = new MySqlStatementParser(sql);
				SQLStatement statement = parser.parseStatement();
				SQLAlterTableStatement alter = (SQLAlterTableStatement)statement;
				for (SQLAlterTableItem item : alter.getItems()) {
						if (item instanceof SQLAlterTableDropIndex) {
								SQLAlterTableDropIndex dropIndex = (SQLAlterTableDropIndex) item;
								System.out.println("删除的索引名为： " + dropIndex.getIndexName());
						} else if (item instanceof SQLAlterTableDropColumnItem){
								SQLAlterTableDropColumnItem dropColumn = (SQLAlterTableDropColumnItem)item;
								System.out.println("删除的列名为： " + dropColumn.getColumns());
						} else if (item instanceof SQLAlterTableAddIndex) {
								SQLAlterTableAddIndex addIndex = (SQLAlterTableAddIndex) item;
								if (addIndex.getName() != null) {
										String indexName = addIndex.getName().getSimpleName();
										System.out.println("新增的索引名为 ： " + indexName);
								}
						} else if (item instanceof SQLAlterTableAddColumn) {
								SQLAlterTableAddColumn addColumn = (SQLAlterTableAddColumn) item;
								System.out.println("新增的列为： " + addColumn.getColumns());
						}
				}
		}

		@Test
		public void testUpdate(){
				String sql = "UPDATE table_test_3 SET run_title='maple' WHERE run_id = '1';";
				MySqlStatementParser parser = new MySqlStatementParser(sql);
				SQLStatement sqlStatement = parser.parseStatement();
				MySqlUpdateStatement updateStatement = (MySqlUpdateStatement)sqlStatement;
				SQLExpr whereExpr = updateStatement.getWhere();
				if(whereExpr instanceof SQLInListExpr){
						// SQLInListExpr 指 run_id in ('1', '2') 这一情况
						SQLInListExpr inListExpr = (SQLInListExpr)whereExpr;
						List<SQLExpr> valueExprs = inListExpr.getTargetList();
						for(SQLExpr expr : valueExprs){
								System.out.print(expr + "\t");
						}
				} else {
						// SQLBinaryOpExpr 指 run_id = '1' 这一情况
						SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) whereExpr;
						System.out.println(binaryOpExpr.getLeft() + " --> " + binaryOpExpr.getRight());
				}
		}

		@Test
		public void testInsert(){
				String sql = "insert into table_test_2 (farendma, hesuandm, hesuanmc, weihguiy, weihjigo, weihriqi, shijchuo) values\n" +
								"('99996','HS205301','代码1','S####','101001','20140101',1414673101376), \n" +
								"('99996','HS205401','代码2','S####','101001','20140101',1414673101376);";
				MySqlStatementParser parser = new MySqlStatementParser(sql);
				SQLStatement sqlStatement = parser.parseStatement();
				MySqlInsertStatement insertStatement = (MySqlInsertStatement)sqlStatement;

				//获取列的名称
				List<SQLExpr> columnExprs = insertStatement.getColumns();
				System.out.println("列的名称为：");
				for(SQLExpr expr : columnExprs){
						System.out.print(expr + "\t");
				}
				System.out.println();

				//获取插入的值
				List<SQLInsertStatement.ValuesClause> valuesClauseList = insertStatement.getValuesList();
				System.out.println("值分别是：");
				for(SQLInsertStatement.ValuesClause valuesClause : valuesClauseList){
						List<SQLExpr> valueExprList = valuesClause.getValues();
						for(SQLExpr expr : valueExprList){
								System.out.print(expr + "\t");
						}
						System.out.println();
				}
		}

		@Test
		public void testFormatMysql(){
				String sql = "insert into table_test_1 select * from table_test_2;";
				String result = SQLUtils.format(sql, JdbcConstants.MYSQL);
				System.out.println("格式化后输出：\n" + result);
		}

}
